package com.cl.ks.utils;

import com.cl.ks.config.ValueConfig;
import com.cl.ks.entity.KsDataField;
import com.cl.ks.entity.SysSourceConfig;
import com.cl.ks.entity.SysSourceMappingTable;
import com.cl.ks.entity.SysSourceMappingTableField;
import com.cl.ks.service.SysSourceConfigService;
import com.cl.ks.service.SysSourceMappingTableFieldService;
import com.cl.ks.service.SysSourceMappingTableService;
import com.jandar.ds.pojo.DbInfo;
import com.jandar.ds.service.DbInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;

import static com.cl.ks.constant.FieldConstants.RESERVED_WORDS;

/**
 * @author xiongxx
 * @date 2022/5/6 16:33
 * @description
 */
@Component
public class DbUtil extends com.cl.pile.rule.utils.DbUtil {
    @Autowired
    DbInfoService dbInfoService;
    @Autowired
    DataSourceMap dataSourceMap;
    @Autowired
    SysSourceConfigService sysSourceConfigService;
    @Autowired
    SysSourceMappingTableService sysSourceMappingTableService;
    @Autowired
    SysSourceMappingTableFieldService sysSourceMappingTableFieldService;
    @Autowired
    ValueConfig valueConfig;

    public List<String> listFieldName(String dbCode, String tableName) {
        try {
            List<String> res = new ArrayList<>();
            List<Map<String, Object>> fieldList = showFullColumns(dbCode, tableName);
            fieldList.forEach(field -> {
                String fieldName = String.valueOf(field.getOrDefault("Field", ""));
                res.add(fieldName);
            });
            return res;
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        }

    }

    public List<Map<String, String>> listField(String dbCode, String tableName) {
        List<Map<String, String>> res = new ArrayList<>();
        List<Map<String, Object>> fieldList = showFullColumns(dbCode, tableName);
        fieldList.forEach(field -> {
            String fieldName = String.valueOf(field.getOrDefault("Field", ""));
            String comment = String.valueOf(field.get("Comment"));
            if (!StringUtils.hasLength(comment)) {
                comment = fieldName;
            }
            String fieldType = String.valueOf(field.getOrDefault("Type", ""));
            Map<String, String> item = new TreeMap<>();
            item.put("fieldName", fieldName);
            item.put("fieldComment", comment);
            item.put("fieldType", fieldType);
            res.add(item);
        });
        return res;
    }

    public Integer createSourceConfigAndMapping(String dbCode, String tableName, String power, String alias) {
        if (power.length() > 255) {
            power = power.substring(0, 255);
        }
//        power = "";
        createSysSourceMappingTable(dbCode, tableName, power, alias);
        createSysSourceMappingTableField(dbCode, tableName, power);
        return createSourceConfig(dbCode, tableName, power, alias);
    }

    public Integer createSourceConfig(String dbCode, String tableName, String power, String alias) {
        SysSourceConfig param = new SysSourceConfig();
        param.setDbCode(dbCode);
        param.setTargetTable(tableName);
        List<SysSourceConfig> exist = sysSourceConfigService.findAllByEntity(param);
        if (exist.isEmpty()) {
            SysSourceConfig sysSourceConfig = new SysSourceConfig();
            sysSourceConfig.setTargetTable(tableName);
            sysSourceConfig.setTargetTableName(alias);
            sysSourceConfig.setDbCode(dbCode);
            sysSourceConfig.setPower(power);
            sysSourceConfigService.save(sysSourceConfig);
            return sysSourceConfig.getId();
        } else {
            SysSourceConfig sysSourceConfig = exist.get(0);
            sysSourceConfig.setTargetTableName(alias);
            sysSourceConfigService.save(sysSourceConfig);
        }
        return exist.get(0).getId();
    }

    public void createSysSourceMappingTable(String dbCode, String tableName, String power, String alias) {
        SysSourceMappingTable sysSourceMappingTable = new SysSourceMappingTable();
        sysSourceMappingTable.setTableName(tableName);
        sysSourceMappingTable.setDbCode(dbCode);
        List<SysSourceMappingTable> exist = sysSourceMappingTableService.findAllByEntity(sysSourceMappingTable);
        if (!exist.isEmpty()) {
            sysSourceMappingTable = exist.get(0);
        } else {
            sysSourceMappingTable.setTableAlias(alias);
        }
        sysSourceMappingTable.setPower(power);
        sysSourceMappingTableService.save(sysSourceMappingTable);
    }

    public void createSysSourceMappingTableField(String dbCode, String tableName, String power) {
        List<SysSourceMappingTableField> sysSourceMappingTableFieldList = new ArrayList<>();
        List<Map<String, Object>> fieldList = showFullColumns(dbCode, tableName);
        fieldList.forEach(field -> {
            String fieldName = String.valueOf(field.getOrDefault("Field", ""));
            String comment = String.valueOf(field.get("Comment"));
            if (!StringUtils.hasLength(comment)) {
                comment = fieldName;
            }
            String fieldType = String.valueOf(field.getOrDefault("Type", ""));
            SysSourceMappingTableField sysSourceMappingTableField = new SysSourceMappingTableField();
            sysSourceMappingTableField.setDbCode(dbCode);
            sysSourceMappingTableField.setTableName(tableName);
            sysSourceMappingTableField.setTableField(fieldName);

            List<SysSourceMappingTableField> exist = sysSourceMappingTableFieldService.findAllByEntity(sysSourceMappingTableField);
            if (!exist.isEmpty()) {
                sysSourceMappingTableField = exist.get(0);
            }
            sysSourceMappingTableField.setTableFieldName(comment);
            sysSourceMappingTableField.setTableFieldDataType(fieldType);
            sysSourceMappingTableField.setPower(power);
            sysSourceMappingTableFieldList.add(sysSourceMappingTableField);
        });
        sysSourceMappingTableFieldService.saveAll(sysSourceMappingTableFieldList);
    }


    public List<Map<String, Object>> fetchSQL(String dbCode, String sql) {
        return query(dbCode, sql);
    }

    public void executeSQL(String code, String sql) {
        execute(code, sql);
    }

    public DbInfo getDbInfo(String dbCode) {
        return dbInfoService.get(dbCode);
    }

    /**
     * 查询
     *
     * @param dbId dbInfo表中的id
     * @param sql
     * @param args
     * @return
     */
    public List<Map<String, Object>> query(Integer dbId, String sql, Object... args) {
        DbInfo dbInfo = dbInfoService.get(dbId);
        return query(dbInfo, sql, args);
    }

    public List<Map<String, Object>> query(String code, String sql, Object... args) {
        DbInfo dbInfo = null;
        if (code == null) {
            return query(dbInfo, sql, args);
        }
        dbInfo = dbInfoService.get(code);
        return query(dbInfo, sql, args);
    }

    public List<Map<String, Object>> query(DbInfo dbInfo, String sql, Object... args) {
        DataSource dataSource = dataSourceMap.get(dbInfo);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate.queryForList(sql, args);
    }

    /**
     * 插入、更新或删除数据
     *
     * @param dbId
     * @param sql
     * @param args
     * @return
     */
    public int update(Integer dbId, String sql, Object... args) {
        DbInfo dbInfo = dbInfoService.get(dbId);
        return update(dbInfo, sql, args);
    }

    public int update(String code, String sql, Object... args) {
        DbInfo dbInfo = dbInfoService.get(code);
        return update(dbInfo, sql, args);
    }

    public int update(DbInfo dbInfo, String sql, Object... args) {
        DataSource dataSource = dataSourceMap.get(dbInfo);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        return jdbcTemplate.update(sql, args);
    }

    /**
     * 执行没有返回的sql语句
     *
     * @param dbId
     * @param sql
     */
    public void execute(Integer dbId, String sql) {
        DbInfo dbInfo = dbInfoService.get(dbId);
        execute(dbInfo, sql);
    }

    public void execute(String code, String sql) {
        try {
            DbInfo dbInfo = null;
            if (code == null || code.isEmpty()) {
                execute(dbInfo, sql);
            } else {
                dbInfo = dbInfoService.get(code);
                execute(dbInfo, sql);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public void execute(DbInfo dbInfo, String sql) {
        DataSource dataSource = dataSourceMap.get(dbInfo);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.execute(sql);
    }

    public long insertReturnId(Integer dbId, String sql, Object... args) {
        DbInfo dbInfo = dbInfoService.get(dbId);
        return insertReturnId(dbInfo, sql, args);
    }

    public long insertReturnId(String code, String sql, Object... args) {
        DbInfo dbInfo = dbInfoService.get(code);
        return insertReturnId(dbInfo, sql, args);
    }

    public long insertReturnId(DbInfo dbInfo, String sql, Object... args) {
        DataSource dataSource = dataSourceMap.get(dbInfo);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(con -> {
            PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            return ps;
        }, keyHolder);
        return Objects.requireNonNull(keyHolder.getKey()).longValue();
    }

    public void insert(String dbCode, String tableName, Map<String, String> dataMap) {
        StringBuilder insertSQL = new StringBuilder("insert into " + tableName + " (" + String.join(",", dataMap.keySet()) + ") values ");
//        List<String> list = dataMap.values().stream().map(item -> item.replace("'", "\\'")).collect(Collectors.toList());
//        list = list.stream().map(item -> {
//            if (item.endsWith("\\")) {
//                item = item.substring(0, item.length() - 1);
//            }
//            return item;
//        }).collect(Collectors.toList());
        insertSQL.append(" ('").append(String.join("','", dataMap.values())).append("'),");
        insertSQL.deleteCharAt(insertSQL.length() - 1);
        insertSQL.append(" ON DUPLICATE KEY UPDATE ").append(dataMap.entrySet().stream().filter(entry -> !entry.getKey().equals("create_time")).map(entry -> {
            String key = entry.getKey();
            String value = entry.getValue();
            return key + "='" + value + "'";
        }).collect(Collectors.joining(",")));
        executeSQL(dbCode, insertSQL.toString().replace("''", "null").replace("\\\\'", "\\'"));
    }

    public void batchInsert(String dbCode, String tableName, List<Map<String, String>> dataMapList) {
        StringBuilder insertSQL = new StringBuilder("insert into " + tableName + " (" + String.join(",", dataMapList.get(0).keySet()) + ") values ");

        for (Map<String, String> dataMap : dataMapList) {
            insertSQL.append(" ('").append(String.join("','", dataMap.values())).append("'),");
            insertSQL.deleteCharAt(insertSQL.length() - 1);
        }
        executeSQL(dbCode, insertSQL.toString().replace("''", "null").replace("\\\\'", "\\'"));
    }

    public List<com.alibaba.fastjson.JSONObject> batchInsert(String dbCode, String
            tableName, List<com.alibaba.fastjson.JSONObject> dataList, Set<String> fieldList, boolean autoIncrement) {
        long index = Long.parseLong(String.valueOf(fetchSQL(dbCode, "select count(1) as count from " + tableName).get(0).get("count")));
        StringBuilder insertSQL = new StringBuilder("insert into " + tableName + " (" + String.join(",", fieldList) + ") values ");

        for (com.alibaba.fastjson.JSONObject item : dataList) {
            insertSQL.append(" (");
            for (String s : fieldList) {
                String value = String.valueOf(item.get(s));
                if (autoIncrement && "id".equalsIgnoreCase(s)) {
                    value = String.valueOf(++index);
                    item.put("id", value);
                }
                if (value != null && !value.equals("null")) {
                    insertSQL.append("'").append(value).append("'");
                } else {
                    insertSQL.append("null");
                }
                insertSQL.append(",");
            }
            insertSQL.deleteCharAt(insertSQL.length() - 1);
            insertSQL.append("),");
        }
        insertSQL.deleteCharAt(insertSQL.length() - 1);
        executeSQL(dbCode, insertSQL.toString().replace("''", "null").replace("\\\\'", "\\'"));
        return dataList;
    }

    public void createTableOrAddColumns(String dbCode, String
            tableName, Set<String> fields, Map<String, String> commentMap) {
        ArrayList<String> newFields = new ArrayList<>(fields);
        execute(dbCode, generateCreateTableSQL(tableName, newFields, commentMap));
        generateAddColumnsSQL(dbCode, tableName, newFields, commentMap);

    }

    public String generateCreateTableSQL(String
                                                 tableName, List<String> fields, Map<String, String> commentMap) {
        StringBuilder sql = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
        sql.append(tableName);
        sql.append(" (");
        sql.append(" id bigint AUTO_INCREMENT ,\n" +
                "    constraint table_pk\n" +
                "        primary key (id) ,");

        for (int i = 0; i < fields.size(); i++) {
            String field = fields.get(i);
            if (RESERVED_WORDS.stream().anyMatch(j -> j.equalsIgnoreCase(field))) {
                sql.append("`").append(field).append("`");
            } else {
                sql.append(field);
            }
            // 假设所有字段都是字符串类型，这里需要根据你的实际情况来设定数据类型
            if (field.equals("content")) {
                sql.append(" longtext comment '");
            } else {
                sql.append(" text comment '");
            }
            if (commentMap.get(field) != null && !commentMap.get(field).isEmpty()) {
                sql.append(commentMap.get(field));
            } else {
                sql.append(field);
            }
            sql.append("' ");
            if (i < fields.size() - 1) {
                sql.append(", ");
            }
        }

        if ("dm".equals(valueConfig.getProfilesActive())) {
            sql.append(");");
        } else {
            sql.append(") ENGINE=MyISAM;");
        }
        return sql.toString();
    }

    public void generateAddColumnsSQL(String dbCode, String
            tableName, List<String> fields, Map<String, String> commentMap) {
        if (fields == null || fields.isEmpty()) {
            return;
        }

        List<String> existingColumns = getExistingColumns(dbCode, tableName).stream().map(String::toUpperCase).collect(Collectors.toList()); // 假设这是一个获取现有列名的函数

        List<String> newColumns = fields.stream()
                .filter(field -> !existingColumns.contains(field.toUpperCase())) // 过滤出新列名
                .collect(Collectors.toList());

        if (newColumns.isEmpty()) {
            return;
        }

        if ("dm".equals(valueConfig.getProfilesActive())) {
            for (String field : newColumns) {
                StringBuilder sql = new StringBuilder();
                sql.append("ALTER TABLE ").append(tableName).append(" ADD COLUMN ");
                if (RESERVED_WORDS.stream().anyMatch(j -> j.equalsIgnoreCase(field))) {
                    sql.append("`").append(field).append("`");
                } else {
                    sql.append(field);
                }
                sql.append(" text ; ");
                execute(dbCode, sql.toString());
            }
        } else {
            StringBuilder sql = new StringBuilder("ALTER TABLE ");
            sql.append(tableName);
            for (int i = 0; i < newColumns.size(); i++) {
                String field = newColumns.get(i);
                sql.append(" ADD COLUMN ");
                if (RESERVED_WORDS.stream().anyMatch(j -> j.equalsIgnoreCase(field))) {
                    sql.append("`").append(field).append("`");
                } else {
                    sql.append(field);
                }

                sql.append(" text comment '");
                if (commentMap.get(field) != null && !commentMap.get(field).isEmpty()) {
                    sql.append(commentMap.get(field));
                } else {
                    sql.append(field);
                }
                sql.append("' "); // 这里只是一个示例，你可能需要根据你的实际需求设定数据类型
                if (i < newColumns.size() - 1) {
                    sql.append(", ");
                }
            }
            sql.append(";");
            execute(dbCode, sql.toString());
        }

    }

    // 示例函数，你需要根据你的数据库连接和方言来实现这部分逻辑来获取现有列名
    private List<String> getExistingColumns(String dbCode, String tableName) {
        List<Map<String, Object>> maps = showFullColumns(dbCode, tableName);
        return maps.stream().map(i -> String.valueOf(i.get("Field"))).collect(Collectors.toList());
    }

    public List<Map<String, Object>> showFullColumns(String dbCode, String tableName) {
        String profilesActive = valueConfig.getProfilesActive();
        if ("dm".equals(profilesActive)) {
            return fetchSQL(dbCode, "SELECT \n" +
                    "    utc.COLUMN_NAME AS \"Field\",\n" +
                    "    ucc.COMMENTS AS \"Comment\",\n" +
                    "    utc.DATA_TYPE AS \"Type\"\n" +
                    "FROM \n" +
                    "    USER_TAB_COLUMNS utc\n" +
                    "JOIN \n" +
                    "    USER_COL_COMMENTS ucc\n" +
                    "ON \n" +
                    "    utc.TABLE_NAME = ucc.TABLE_NAME AND utc.COLUMN_NAME = ucc.COLUMN_NAME\n" +
                    "WHERE \n" +
                    "    utc.TABLE_NAME = '" + tableName + "';");
        } else {
            return fetchSQL(dbCode, "show full columns from " + tableName);
        }
    }
}

